PART II: Update Data and Analyze

This is the second part of a two-part vignette designed to collect and analyze Twitter posts from Boston and Guatemala by searching for location hashtags. This part of the vignette carries out the database initialization and initial status collections. This part of the vignette consists of two sub-parts: (a) updating the twitter database with new posts, and (b) analysis. These two sub-parts can be run together or independently, as often as required.

Initialization

We begin by loading the twinfoR namespace, establishing the Twitter database connection to the sqlite database created in Part I of this vignette, and loading the authentication vector. See (and execute) the location_search_1 vignette if you haven’t already done so.

This code assumes the auth.vector from Part I is saved. If it isn’t, reauthenticate using the authorize_IT() function as done in Part I.

library(twinfoR)
setwd("~/twitter_locations")
con <- twitter_database("locations.sqlite")
load("auth_vector.RData") 

Update Database with New Statuses

This function should take considerably less time to run than the initial call, because it only collects new statuses.

update_search(
  con
)

# Summarize database
summarize_database(con)
#> No query user table.
#> Search queries: 4
#> Search categories:
#> location
#> since_id
#> Users: 11454
#> Statuses: 21860
#> Unique user mentions: 5633
#> Unique hashtags: 16021
#> Unique URLs: 7303
#> Total Images: 6385
#> Unique Images: 5472
#> Total relationships: 0
#> Database file size: 88MB

Analysis

The summary output from the last section shows the columns of the query_text table. Let’s take a closer look at this table, which we created in Part I of this vignette.

df <- DBI::dbReadTable(con,"query_text")
knitr::kable(
  df,
  caption = "The `query_text` table"
)

Any column in this table can be used as where.criteria in the analysis functions. We are going to use the location column.

tophashtags.boston <- top_hashtags(
  con,
  start.date = Sys.Date()-7,
  where.criteria = "query_text.location = 'Boston'"
)
names(tophashtags.boston) = c("Boston.Hashtag.Lower","Count","Boston.Hashtag")
tophashtags.boston$Boston.Hashtag <- paste("#",tophashtags.boston$Boston.Hashtag,sep="")

tophashtags.GC <- top_hashtags(
  con,
  start.date = Sys.Date()-7,
  where.criteria = "query_text.location = 'Guatemala City'"
)
names(tophashtags.GC) = c("GuatemalaCity.Hashtag.Lower","Count","GuatemalaCity.Hashtag")
tophashtags.GC$GuatemalaCity.Hashtag <- paste("#",tophashtags.GC$GuatemalaCity.Hashtag,sep="")

knitr::kable(
  list(
    tophashtags.boston[,c("Boston.Hashtag","Count")],
    tophashtags.GC[,c("GuatemalaCity.Hashtag","Count")]
  ),
  caption = "Top Hashtags Used by Boston and Guatemala City"
)

topusermentions.boston <- top_usermentions(
  con,
  start.date = Sys.Date()-7,
  where.criteria = "query_text.location = 'Boston'"
)

topusermentions.GC <- top_usermentions(
  con,
  start.date = Sys.Date()-7,
  where.criteria = "query_text.location = 'Guatemala City'"
)


knitr::kable(
  data.frame(
    Screen_Name = paste("@",topusermentions.boston$user_mention_screen_name,sep=""),
    Name = topusermentions.boston$user_mention_name,
    Mentions = topusermentions.boston$n,
    stringsAsFactors = FALSE
  ),
  caption = "Boston User Mentions"
)

knitr::kable(
  data.frame(
    Screen_Name = paste("@",topusermentions.GC$user_mention_screen_name,sep=""),
    Name = topusermentions.GC$user_mention_name,
    Mentions = topusermentions.GC$n,
    stringsAsFactors = FALSE
  ),
  caption = "Guatemala City User Mentions"
)

Top URLs posted by Boston and Guatemala City in the last week

topurls.boston <- top_urls(
  con,
  start.date = Sys.Date()-7,
  where.criteria = "query_text.location = 'Boston'"
)

topurls.GC <- top_urls(
  con,
  start.date = Sys.Date()-7,
  where.criteria = "query_text.location = 'Guatemala City'"
)


knitr::kable(
  list(
    Boston = data.frame(
      Boston.URL = topurls.boston$extended_url,
      Count = topurls.boston$n,
      stringsAsFactors = FALSE
    ),
    "Guatemala City" = data.frame(
      GuatemalaCity.URL = topurls.GC$extended_url,
      Count = topurls.GC$n,
      stringsAsFactors = FALSE
    )
  ),
  caption = "Top Boston and Guatemala City URLs"
)

Top Boston and Guatemala City URLs

Boston.URL Count http://www.thewizardofleather.com 306 http://natsjobs.com/find-a-job/?search_location=Boston 99 http://thewizardofleather.com 71 http://bit.ly/YAC1Ic 53 http://www.bostonsegwaytours.net 38 http://www.bostonsegwaytoursinc.com/gift 37 http://line.me/S/sticker/1270761 34 http://twib.in/l/LRyMbLaL5xXR 31 http://smarturl.it/Rahzarspitarhyme/applemusic 28 https://bit.ly/2GYcdUQ 24

GuatemalaCity.URL Count https://buff.ly/2VPQh1v 2 https://www.instagram.com/p/BslblXBA7N0/?utm_source=ig_twitter_share&igshid=1cm5mpwyb9ngp 2 http://bit.ly/2FHVF0T 1 http://www.citymax-mix.com 1 https://buff.ly/2sBl9p6 1 https://newsinamerica.com/pdcc/aries-el-lider-presenta-encima-de-mi/ 1 https://open.spotify.com/track/2zfm5s9Db7ppg9kt3G1gH1 1 https://twitter.com/ABC7Chicago/status/1083921649488015360 1 https://twitter.com/muniguate/status/1085571579994689541 1 https://twitter.com/muniguate/status/1085578070604558337 1

Top media from Boston and Guatemala City in the last week

topmedia.boston <- top_media(
  con,
  start.date = Sys.Date()-14,
  where.criteria = "query_text.location = 'Boston'",
  save.to.file = FALSE
)

topmedia.GC <- top_media(
  con,
  start.date = Sys.Date()-14,
  where.criteria = "query_text.location = 'Guatemala City'",
  save.to.file = FALSE
)

knitr::kable(
  list(
    data.frame(
      Boston.image_url = topmedia.boston$media_url,
      count = topmedia.boston$n,
      stringsAsFactors = FALSE
    ),
    data.frame(
      GuatemalaCity.image_url = topmedia.GC$media_url,
      count = topmedia.GC$n,
      stringsAsFactors = FALSE
    )
  ),
  caption = "Top Media Posted by Boston and Guatemala City"
)

Top Media Posted by Boston and Guatemala City

Boston.image_url count http://pbs.twimg.com/media/Dw0l87FX0Aw9ui_.jpg 39 http://pbs.twimg.com/media/Dw0l87IX0AMJ3YR.jpg 39 http://pbs.twimg.com/media/DwqcmABUcAIySiw.jpg 38 http://pbs.twimg.com/media/Dpp_1YGWkAAhA6-.jpg 36 http://pbs.twimg.com/amplify_video_thumb/1083439532240973824/img/JXjcTq1tcj96dGPC.jpg 34 http://pbs.twimg.com/media/CgIqd39UMAA3Lva.jpg 16 http://pbs.twimg.com/media/CgK6_7kUAAAcckG.jpg 16 http://pbs.twimg.com/media/Dw4rfI1WsAEDeuF.jpg 13 http://pbs.twimg.com/media/Dm7OrVFXcAELAXk.jpg 11 http://pbs.twimg.com/media/Dw5xxAJWoAI0xjf.jpg 11

GuatemalaCity.image_url count http://pbs.twimg.com/media/DwjddPEWoAERPkK.jpg 3 http://pbs.twimg.com/media/DwefXouWkAU5kBS.png 1 http://pbs.twimg.com/media/DwefZdiWoAEkA23.png 1 http://pbs.twimg.com/media/DwefbNGWoAMGIub.jpg 1 http://pbs.twimg.com/media/DwleQsFV4AAA7Vr.jpg 1 http://pbs.twimg.com/media/DwleRKWU8AIJ6px.jpg 1 http://pbs.twimg.com/media/DwupUUfUUAA7Ita.jpg 1 http://pbs.twimg.com/media/DwupV4dV4AAphk3.jpg 1 http://pbs.twimg.com/media/DwvEqXdW0AAzb1Q.jpg 1 http://pbs.twimg.com/media/DwvEyZ6WoAAdQre.jpg 1

## Top Boston Image (Not Run)

# status_media(
#   topmedia.boston[1,],
#   con, 
#   update.media.b64 = FALSE,
#   display.image = TRUE
# )

## Top Guatemala City Image (Not Run)

# status_media(
#   topmedia.GC[1,],
#   con,
#   update.media.b64 = FALSE,
#   display.image = TRUE
# )

Top Boston Image	Top Guatemala City Image

The top_media and status_media have many options for saving and rendering images. See documentation for details.

Top tweeters Boston and Guatemala City in the last week

toptweeters.boston <- top_tweeters(
  con,
  start.date = Sys.Date()-7,
  where.criteria = "query_text.location = 'Boston'"
)

toptweeters.GC <- top_tweeters(
  con,
  start.date = Sys.Date()-7,
  where.criteria = "query_text.location = 'Guatemala City'"
)

toptweeters.boston$description <- gsub("\n"," -- ",toptweeters.boston$description)
toptweeters.boston$location <- gsub("\n"," -- ",toptweeters.boston$location)
toptweeters.GC$description <- gsub("\n"," -- ",toptweeters.GC$description)
toptweeters.GC$location <- gsub("\n"," -- ",toptweeters.GC$location)
knitr::kable(
  data.frame(
    Name = toptweeters.boston$name,
    Location = toptweeters.boston$location,
    Description = toptweeters.boston$description,
    Tweets = toptweeters.boston$n,
    stringsAsFactors = FALSE
  ),
  caption = "Top Boston Tweeters"
)

Top Boston Tweeters

Name	Location	Description	Tweets
Truth Tweeter	Corrupt City of Chicago	I love America. I love our Constitution. I hope we return to our LIBERTARIAN values! #MAGA	466
María Navarro	Houston, TX	Team 🧙🏻‍♂️followizard❤️ #f4f #FollowMe #followback #Follow4follow #Followher #USA🇺🇸🇺🇸 #Houston #Texas	184
Roxanne Shelaider	Malibu, CA	🇺🇸🇺🇸🇺🇸🇺🇸🇺🇸 I´m a #Malibu fashion lover team #followizard #Followme #follow4follow, #f4f #Followback #follower #USA 🇺🇸🇺🇸🇺🇸🇺🇸🇺🇸	169
Nathalie Watson	Ottawa, Ontario	America’s 1st Job Alert System	98
TheRealBostonSegway	Boston, MA	Don’t be fooled, there’s only one! Right in the heart of it all at 199 State St. and we are The best way to see the city! 617-421-1234 https://t.co/RTeeSA3dL8	90
Boston Plow	Internet	A new #Boston community with less noise & junk on @plowio. Follow for an invite to join our open beta!	89
#Londonislovinit	London	Promoting everything that is #LONDON under the Hashtag #Londonislovinit Use this 4 a RT everytime #Londonhour every Thurs 8-9pm adam@atsocialmedia.co.uk 👊	89
Boston Jobs	Boston, MA	Find Jobs and Work in Boston, Massachusetts at https://t.co/OZ8R0vTPpw! – – #BOSTON #MA #WORK #JOBS #CAREER #EMPLOY #CELTICS #REDSOX #BRUINS #CANNONS #BLADES	71
East Coast Sports	FT Lauderdale, FLA	Welcome to East Coast Sports! We Specialize in RARE Autographed New England Sports Memorabilia, and RARE items signed by Legends of the game both Past & Present	69
Timothy Breitzmann	Chicago	Follow my link, check out my art @ Society6. We need to put down our hate and reach for Enlightenment or this Human experiment will not end well for any of us.	68

knitr::kable(
  data.frame(
    Name = toptweeters.GC$name,
    Location = toptweeters.GC$location,
    Description = toptweeters.GC$description,
    Tweets = toptweeters.GC$n,
    stringsAsFactors = FALSE
  ),
  caption = "Top Guatemala City Tweeters"
)

Top Guatemala City Tweeters

Name	Location	Description	Tweets
IO al Desnudo ™🇬🇹	Guatemala	Comunicólogo, #AmoGuate #BuenaVibra	10
Raúl Contreras	Guatemala	En la vida nada es caSUalidad, todo es caUSalidad. ¡Así es la vida!	5
Jfranco87	Guatemala	Café 87	5
Guatemala			4
Zo	United States	GOD . FAMILY . TRAVELER . RISK TAKER . AAMU ALUM . GLOBAL MARKETING	4
CityMax Mix	Guatemala	#1 en Bienes Raices	4
вяαd αѕтυяιαѕ t(’’t•	Guatemala	Fisicoculturista Guatemalteco 🇬🇹 ™🏃20 años de edad •Entrenador Personal 💯🏋🏽Instagram- bradasturias 👻Snap- brad_asturias #Björk 🖤	3
CITYMAX Guatemala	Guatemala	Franquicia Bienes Raices Ciudad Guatemala, Apartamentos, Casas, Locales Comerciales, Edificios, Terrenos, Oficinas y Bodegas Renta/Venta ☎️PBX(502)2493-5555	3
Spade Graphics by Dave OfTheJungle	Guatemala	Proud member of Zoey Love Art and Spade Graphics	3
Nancy Yarelis Cortez Surque	Guatemala		3

Most liked tweets Boston and Guatemala City in the last week

mostliked.boston <- most_liked(
  con,
  start.date = Sys.Date()-7,
  where.criteria = "query_text.location = 'Boston'"
)

mostliked.GC <- most_liked(
  con,
  start.date = Sys.Date()-7,
  where.criteria = "query_text.location = 'Guatemala City'"
)

# For nice Markdown rendering
mostliked.boston$description <- gsub("\n"," -- ",mostliked.boston$description)
mostliked.boston$location <- gsub("\n"," -- ",mostliked.boston$location)
mostliked.boston$text <- gsub("\n"," -- ",mostliked.boston$text)
mostliked.boston$name <- gsub("\n"," -- ",mostliked.boston$name)
mostliked.GC$description <- gsub("\n"," -- ",mostliked.GC$description)
mostliked.GC$location <- gsub("\n"," -- ",mostliked.GC$location)
mostliked.GC$text <- gsub("\n"," -- ",mostliked.GC$text)
mostliked.GC$name <- gsub("\n"," -- ",mostliked.GC$name)
knitr::kable(
  data.frame(
    Name = mostliked.boston$name,
    Created = mostliked.boston$created_at,
    Likes = mostliked.boston$favorite_count,
    Text = mostliked.boston$text,
    stringsAsFactors = FALSE
  ),
  caption = "Most Liked Boston Tweets"
)

Most Liked Boston Tweets

Name	Created	Likes	Text
Joey McIntyre	2019-01-14 04:05:01	955	Great day for #Boston- @patriots win big and so does Kira Mac 😊 – 1st All-Around at Boston Invitational. – #proudpapa #kiwakiwa 😭 https://t.co/wyQn3FKKue
Sarah Shahi	2019-01-14 23:18:39	703	You’ll want to 🤛🏼 her and 💋 her at the same time. Meet Rachel. Get ready. She’s dynamite in a tiny package @BenAffleck @AldisHodge @jonathanmtucker @Showtime #Boston #wickedsmaht https://t.co/TKaDV9PQM6
BostonTweet	2019-01-11 22:19:13	424	Brrrr #Boston https://t.co/WtYCPP6cIA
Fernanda Raverta	2019-01-16 17:41:18	351	¿Qué es la pedagogía de la crueldad? – – Un operativo en plena madrugada con más de 200 efectivos para desalojar a trabajadores que fueron víctimas del vaciamiento de una empresa. – – Mi mayor repudio a este accionar de la Justicia y mi solidaridad con los trabajadores de la #Boston https://t.co/FgzdBs5iuI
David Wade	2019-01-16 00:12:01	329	Of all the stores that have closed in #Boston through the years, Filene’s Basement is the one I miss most. And it’s not even close. How about you? https://t.co/19wfYHaN7b
Red	2019-01-13 21:36:18	312	Daily reminder that Gods walk among us. #Boston #Sports #Blessed https://t.co/oHEtAbc1S5
Ayanna Pressley	2019-01-15 23:10:23	296	Point of clarification: Just so there is no confusion or trouble, especially on today. I am not a member of #AKA I am however a proud sister of @linksinc #Boston chapter. Many of my link sisters & new #CBC colleagues (see Lauren below) are Happy #FoundersDay #HU #1908 #sisterhood https://t.co/lpmMtXLLpY
Luis Bremer	2019-01-16 14:19:24	175	Donde había una fuente de trabajo y las medialunas más ricas de Mardel….. #Tristeza #Boston #MardelPlata https://t.co/4UAOZgjCRk
Ahmed/DaBaby Fan Account	2019-01-15 22:19:39	163	i think Carmelo Anthony would be perfect for the @celtics , they need the veteran leadership that Kyrie is looking for right now in their recent struggles. – – #CelticNation #Boston https://t.co/HNUi91CB3e
Kassandra Seven	2019-01-13 14:09:00	153	This food bank is serving #Boston-area #USCoastGuard members who are not being paid due to the #shutdown. – – Please spread the word and consider donating. – – Massachusetts Military Support Foundation https://t.co/Xklih0IxFQ

knitr::kable(
  data.frame(
    Name = mostliked.GC$name,
    Created = mostliked.GC$created_at,
    Likes = mostliked.GC$favorite_count,
    Text = mostliked.GC$text,
    stringsAsFactors = FALSE
  ),
  caption = "Most Liked Guatemala City Tweets"
)

Most Liked Guatemala City Tweets

Name	Created	Likes	Text
José E. Valdizán	2019-01-14 14:38:08	6	Edificio del Banco Industrial y Plazuela 11 de marzo #guatemala #guatemalacity #streets #guate #streetstyle #streetview #view #streetlife #city #citylife #centralamerica #dji… https://t.co/GmIbX4nR9b
CruzRojaGT	2019-01-12 17:53:25	6	#CiudadDeGuatemala – – Accidente de tránsito se suscita en 8av 2Calle zona 1, dos vehículos colisionan, socorristas de #CruzRojaGT le brindaron atención prehospitalaria a una persona con heridas leves en el lugar, no ameritó ser trasladada. https://t.co/rCUw93iwvT
TEMBLOR ⚡️	2019-01-12 19:53:24	4	🇬🇹➡#Temblor 4.3⚡ ⏱12:51 Pm H/Guatemala🇬🇹 – 🔶#SanJose Puerto a 62 Km al Sur de #Iztapa – Profundidad 🔰43 Kms – Sur de #CiudadDeGuatemala – D. de #Escuintla – #Sismo #FelizSabado – #Earthquake #12Ene – #TemblorGT https://t.co/5hF1R0T03x
José E. Valdizán	2019-01-18 07:05:49	3	Vista Hermosa zona 15, Guatemala #cityscape #city #guatemala #guatemalacity #guate #guategram #view #drone #droneview #dronestagram #vistahermosaguatemala #life #citylife #citylifestyle… https://t.co/IX3ImlKSel
CityMax Mix	2019-01-16 20:55:37	3	Apartamento con Linea Blanca en Parque 11 – – Renta Q5,000.00 mantenimiento incluido, dos habitaciones, 2 baños. – – #CityMaxMIX #RealEstateGuatemala #GuatemalaCity #realestateMIX #TuProfesionalInmobiliario #RealEstate #Mixco https://t.co/voPzn9Xm3Q
TEMBLOR ⚡️	2019-01-12 19:52:50	3	🇬🇹➡#Temblor 4.3 ⚡ ⏱12:51 Pm H/Guatemala🇬🇹 – 🔶#SanJose Puerto a 62 Km al Sur de #Iztapa – Profundidad 🔰43 Kms – Sur de #CiudadDeGuatemala – D. de #Escuintla – #Sismo #FelizSabado – #Earthquake #12Ene – #TemblorGT https://t.co/ze8zg8k183
CityMax Mix	2019-01-15 21:18:26	2	¡Bodega en San Cristobal! – - Terreno de 9 por 20 – - 325m² totales de construcción – - Mezzanine de 56m² – - 1.5 baños – - Energía eléctrica bifasica – - Instalación de agua potable municipal – - 8 espacios para estacionamiento – #CityMaxMIX #RealEstateGuatemala #GuatemalaCity #realestateMIX https://t.co/Vekq3mCnop
CityMax Mix	2019-01-14 20:41:57	2	¡Casa cerca de Sankris Mall! – – De dos niveles, con 3 habitaciones con closets, 2 baños completos, espacio para 2 vehículos. – – Venta Q700,000.00 – – #CityMaxMIX #RealEstateGuatemala #GuatemalaCity #realestateMIX #TuProfesionalInmobiliario #RealEstate #Mixco https://t.co/9jg1i00xXR
вяαd αѕтυяιαѕ t(’’t•	2019-01-11 02:54:14	2	⭕️⭕️ – – #BradAsturias #TeamArango #LOCO #PersonalTrainer #ChaosGuatemala #Diet – #Ripped #alphanutrition #Guatemalacity #generationiron #fitnessgym #bodybuilding #training #best… https://t.co/dH6DRnCIHm
goldmindslatino	2019-01-17 15:11:36	2	#conferencia #presencial de #forex este 26 de Enero en #ciudaddeguatemala. #aprende Con nosotros sin Redes de #mercadeo ni pagos mensuales. Pares de enfoque: #xauusd #gbpjpy. #estrategias de otro #nivel totalmente en #español

Most retweeted tweets Boston and Guatemala City in the last week

mostretweeted.boston <- most_retweeted(
  con,
  start.date = Sys.Date()-7,
  where.criteria = "query_text.location = 'Boston'"
)

mostretweeted.GC <- most_retweeted(
  con,
  start.date = Sys.Date()-7,
  where.criteria = "query_text.location = 'Guatemala City'"
)

# For nice Markdown rendering
mostretweeted.boston$description <- gsub("\n"," -- ",mostretweeted.boston$description)
mostretweeted.boston$location <- gsub("\n"," -- ",mostretweeted.boston$location)
mostretweeted.boston$text <- gsub("\n"," -- ",mostretweeted.boston$text)
mostretweeted.boston$name <- gsub("\n"," -- ",mostretweeted.boston$name)
mostretweeted.GC$description <- gsub("\n"," -- ",mostretweeted.GC$description)
mostretweeted.GC$location <- gsub("\n"," -- ",mostretweeted.GC$location)
mostretweeted.GC$text <- gsub("\n"," -- ",mostretweeted.GC$text)
mostretweeted.GC$name <- gsub("\n"," -- ",mostretweeted.GC$name)
knitr::kable(
  data.frame(
    Name = mostretweeted.boston$name,
    Created = mostretweeted.boston$created_at,
    Retweet_Count = mostretweeted.boston$retweet_count,
    Text = mostretweeted.boston$text,
    stringsAsFactors = FALSE
  ),
  caption = "Most Retweeted Boston Tweets"
)

Most Retweeted Boston Tweets

Name	Created	Retweet_Count	Text
Fernanda Raverta	2019-01-16 17:41:18	268	¿Qué es la pedagogía de la crueldad? – – Un operativo en plena madrugada con más de 200 efectivos para desalojar a trabajadores que fueron víctimas del vaciamiento de una empresa. – – Mi mayor repudio a este accionar de la Justicia y mi solidaridad con los trabajadores de la #Boston https://t.co/FgzdBs5iuI
MoreCoin	2019-01-11 17:30:19	196	Which $MORE party are you going to? #Hollywood #OC #Boston #Miami #Chicago? 18 places to use crypto. #usecase #adoption #iwantmore #bittrex #blockchain #cryptotequila https://t.co/7YuE4EvPlW
Kassandra Seven	2019-01-13 14:09:00	155	This food bank is serving #Boston-area #USCoastGuard members who are not being paid due to the #shutdown. – – Please spread the word and consider donating. – – Massachusetts Military Support Foundation https://t.co/Xklih0IxFQ
Sarah Shahi	2019-01-14 23:18:39	79	You’ll want to 🤛🏼 her and 💋 her at the same time. Meet Rachel. Get ready. She’s dynamite in a tiny package @BenAffleck @AldisHodge @jonathanmtucker @Showtime #Boston #wickedsmaht https://t.co/TKaDV9PQM6
Luis Bremer	2019-01-16 14:19:24	75	Donde había una fuente de trabajo y las medialunas más ricas de Mardel….. #Tristeza #Boston #MardelPlata https://t.co/4UAOZgjCRk
Joey McIntyre	2019-01-14 04:05:01	65	Great day for #Boston- @patriots win big and so does Kira Mac 😊 – 1st All-Around at Boston Invitational. – #proudpapa #kiwakiwa 😭 https://t.co/wyQn3FKKue
It’s Going Down	2019-01-18 11:06:24	63	Saturday in #Boston, a variety of anti-racist organizations are mobilizing against a group of neo-Nazis with Resist Marxism - who are planning to crash the #WomensMarch. A small group of #ProudBoys also claim they will wear disguises to attend in #Orlando. https://t.co/DvzFhHDz47 https://t.co/bwF6XT7VrL
Carbon Black, Inc.	2019-01-14 21:27:23	63	Honored to be named the best place to work in #Boston this year by @BuiltinBOS. We remain committed to investing in the growth of our employees, and are excited for the opportunities that #2019 has in store for us - https://t.co/CCCHBQToWe
Today In History	2019-01-17 05:14:00	59	Today in 1706: American #statesman Benjamin #Franklin is born in #Boston. He is the youngest boy in a family of 17 children. Among his many accomplishments, he helped draft the Declaration of #Independence. He died on April 17, 1790. #history #AmericanHistory https://t.co/hcVqQB5xOl
Ahmed/DaBaby Fan Account	2019-01-15 22:19:39	51	i think Carmelo Anthony would be perfect for the @celtics , they need the veteran leadership that Kyrie is looking for right now in their recent struggles. – – #CelticNation #Boston https://t.co/HNUi91CB3e

knitr::kable(
  data.frame(
    Name = mostretweeted.GC$name,
    Created = mostretweeted.GC$created_at,
    Retweet_Count = mostretweeted.GC$retweet_count,
    Text = mostretweeted.GC$text,
    stringsAsFactors = FALSE
  ),
  caption = "Most Retweeted Guatemala City Tweets"
)

Most Retweeted Guatemala City Tweets

Name	Created	Retweet_Count	Text
CruzRojaGT	2019-01-12 17:53:25	4	#CiudadDeGuatemala – – Accidente de tránsito se suscita en 8av 2Calle zona 1, dos vehículos colisionan, socorristas de #CruzRojaGT le brindaron atención prehospitalaria a una persona con heridas leves en el lugar, no ameritó ser trasladada. https://t.co/rCUw93iwvT
WorldShare	2019-01-18 13:00:34	1	The thousands of people living around the rubbish dump in #GuatemalaCity are exposed to dangerous living conditions daily. Our partner Potter’s House work to provide protection and safety as well as developing self-worth and hope. https://t.co/XTzDySKj0R https://t.co/5lavH76xuM
José E. Valdizán	2019-01-18 07:05:49	1	Vista Hermosa zona 15, Guatemala #cityscape #city #guatemala #guatemalacity #guate #guategram #view #drone #droneview #dronestagram #vistahermosaguatemala #life #citylife #citylifestyle… https://t.co/IX3ImlKSel
CityMax Mix	2019-01-16 20:55:37	1	Apartamento con Linea Blanca en Parque 11 – – Renta Q5,000.00 mantenimiento incluido, dos habitaciones, 2 baños. – – #CityMaxMIX #RealEstateGuatemala #GuatemalaCity #realestateMIX #TuProfesionalInmobiliario #RealEstate #Mixco https://t.co/voPzn9Xm3Q
CityMax Mix	2019-01-15 21:18:26	1	¡Bodega en San Cristobal! – - Terreno de 9 por 20 – - 325m² totales de construcción – - Mezzanine de 56m² – - 1.5 baños – - Energía eléctrica bifasica – - Instalación de agua potable municipal – - 8 espacios para estacionamiento – #CityMaxMIX #RealEstateGuatemala #GuatemalaCity #realestateMIX https://t.co/Vekq3mCnop
CityMax Mix	2019-01-14 20:41:57	1	¡Casa cerca de Sankris Mall! – – De dos niveles, con 3 habitaciones con closets, 2 baños completos, espacio para 2 vehículos. – – Venta Q700,000.00 – – #CityMaxMIX #RealEstateGuatemala #GuatemalaCity #realestateMIX #TuProfesionalInmobiliario #RealEstate #Mixco https://t.co/9jg1i00xXR
goldmindslatino	2019-01-17 15:11:36	1	#conferencia #presencial de #forex este 26 de Enero en #ciudaddeguatemala. #aprende Con nosotros sin Redes de #mercadeo ni pagos mensuales. Pares de enfoque: #xauusd #gbpjpy. #estrategias de otro #nivel totalmente en #español
Guatemala	2019-01-16 16:50:19	1	OTRA OPCIÓN AL #TRÁNSITOGT – – De ingreso a #CiudadDeGuatemala por el sur del país. Tome nota de otra vía al #TráficoGT. https://t.co/zpTq2SRywb
TEMBLOR ⚡️	2019-01-12 19:53:24	1	🇬🇹➡#Temblor 4.3⚡ ⏱12:51 Pm H/Guatemala🇬🇹 – 🔶#SanJose Puerto a 62 Km al Sur de #Iztapa – Profundidad 🔰43 Kms – Sur de #CiudadDeGuatemala – D. de #Escuintla – #Sismo #FelizSabado – #Earthquake #12Ene – #TemblorGT https://t.co/5hF1R0T03x
TEMBLOR ⚡️	2019-01-12 19:52:50	1	🇬🇹➡#Temblor 4.3 ⚡ ⏱12:51 Pm H/Guatemala🇬🇹 – 🔶#SanJose Puerto a 62 Km al Sur de #Iztapa – Profundidad 🔰43 Kms – Sur de #CiudadDeGuatemala – D. de #Escuintla – #Sismo #FelizSabado – #Earthquake #12Ene – #TemblorGT https://t.co/ze8zg8k183

mostpopRT.boston <- most_popular_RT_in_sample(
  con,
  start.date = Sys.Date()-7,
  where.criteria = "query_text.location = 'Boston'"
)

mostpopRT.GC <- most_popular_RT_in_sample(
  con,
  start.date = Sys.Date()-7,
  where.criteria = "query_text.location = 'Guatemala City'"
)

# For nice Markdown rendering
mostpopRT.boston$description <- gsub("\n"," -- ",mostpopRT.boston$description)
mostpopRT.boston$location <- gsub("\n"," -- ",mostpopRT.boston$location)
mostpopRT.boston$text <- gsub("\n"," -- ",mostpopRT.boston$text)
mostpopRT.boston$name <- gsub("\n"," -- ",mostpopRT.boston$name)
mostpopRT.GC$description <- gsub("\n"," -- ",mostpopRT.GC$description)
mostpopRT.GC$location <- gsub("\n"," -- ",mostpopRT.GC$location)
mostpopRT.GC$text <- gsub("\n"," -- ",mostpopRT.GC$text)
mostpopRT.GC$name <- gsub("\n"," -- ",mostpopRT.GC$name)
knitr::kable(
  data.frame(
    Name = mostpopRT.boston$name,
    Created = mostpopRT.boston$created_at,
    Dem_Retweets = mostpopRT.boston$n,
    Text = mostpopRT.boston$text,
    stringsAsFactors = FALSE
  ),
  caption = "Most Popular Retweets Among Boston"
)

knitr::kable(
  data.frame(
    Name = mostpopRT.GC$name,
    Created = mostpopRT.GC$created_at,
    Rep_Retweets = mostpopRT.GC$n,
    Text = mostpopRT.GC$text,
    stringsAsFactors = FALSE
  ),
  caption = "Most Popular Retweets Among Guatemala City"
)

mostreach.boston <- most_reach(
  con,
  start.date = Sys.Date()-7,
  where.criteria = "query_text.location = 'Boston'"
)

mostreach.GC <- most_reach(
  con,
  start.date = Sys.Date()-7,
  where.criteria = "query_text.location = 'Guatemala City'"
)

# For nice Markdown rendering
mostreach.boston$description <- gsub("\n"," -- ",mostreach.boston$description)
mostreach.boston$location <- gsub("\n"," -- ",mostreach.boston$location)
mostreach.boston$text <- gsub("\n"," -- ",mostreach.boston$text)
mostreach.boston$name <- gsub("\n"," -- ",mostreach.boston$name)
mostreach.GC$description <- gsub("\n"," -- ",mostreach.GC$description)
mostreach.GC$location <- gsub("\n"," -- ",mostreach.GC$location)
mostreach.GC$text <- gsub("\n"," -- ",mostreach.GC$text)
mostreach.GC$name <- gsub("\n"," -- ",mostreach.GC$name)
knitr::kable(
  data.frame(
    Name = mostreach.boston$name,
    Created = mostreach.boston$created_at,
    Reach = mostreach.boston$n,
    Text = mostreach.boston$text,
    stringsAsFactors = FALSE
  ),
  caption = "Boston Tweets with Most 'Reach'"
)

knitr::kable(
  data.frame(
    Name = mostreach.GC$name,
    Created = mostreach.GC$created_at,
    Reach = mostreach.GC$n,
    Text = mostreach.GC$text,
    stringsAsFactors = FALSE
  ),
  caption = "Guatemala City Tweets with Most 'Reach'"
)

wordcloud_plot(
  con,
  start.date = Sys.Date()-7,
  where.criteria = "query_text.location = 'Boston'",
  caption = "Boston"
)

wordcloud_plot(
  con,
  start.date = Sys.Date()-7,
  where.criteria = "query_text.location = 'Guatemala City'",
  caption = "Guatemala City"
)

timeplot(
  con,
  start.date = Sys.Date()-7,
  where.criteria = "query_text.location = 'Guatemala City' OR query_text.location = 'Boston'",
  group.column = "query_text.location",
  log.scale=TRUE
)
#> Warning: Removed 2 rows containing missing values (geom_path).

# Import igraph
library(igraph)
library(sigmajs)
library(visNetwork)

usermention.query <- sprintf("SELECT LOWER(status.screen_name) as mentioner,LOWER(user_mention.user_mention_screen_name) as mentioned, status.id as status_id, status.text as status_text, status.created_at as created_at FROM status JOIN search_status ON status.id = search_status.status_id JOIN query_text ON search_status.query_id = query_text.id JOIN user_mention ON status.id = user_mention.status_id WHERE status.created_at > '%s' AND query_text.location = 'Boston';",format(Sys.Date()-7))
usermentions.df <- DBI::dbGetQuery(con,usermention.query)
if(nrow(usermentions.df) > 0){
  g <- graph_from_data_frame(usermentions.df)
  vertices.df <- data.frame(index=as.integer(V(g)), screen_name = names(V(g)),stringsAsFactors = FALSE)
}

if(nrow(usermentions.df) > 0){
  ec <- centr_eigen(g,directed=FALSE)
  df <- data.frame(
    Screen_Name = paste(
      "@",
      vertices.df$screen_name,
      sep=""
    ),
    Eig_Centrality = as.numeric(ec$vector)
  )
  df <- df[order(df[,2],decreasing=TRUE),]
  knitr::kable(
    df[1:10,],
    row.names = FALSE,
    caption = "User Mention Network Eigenvector Centrality (Boston)"
  )
}

if(nrow(usermentions.df) > 0){
  bc <- centr_betw(g,directed=FALSE)
  df <- data.frame(
    Screen_Name = paste(
      "@",
      vertices.df$screen_name,
      sep=""
    ),
    Bet_Centrality = as.numeric(as.numeric(bc$res))
  )
  df <- df[order(df[,2],decreasing=TRUE),]
  knitr::kable(
    df[1:10,],
    caption = "User Mention Network Betweenness Centrality (Boston)"
  )
}

if(nrow(usermentions.df) > 0){
  verts <- data.frame(
      name = vertices.df$screen_name,
      label = vertices.df$screen_name,
      title = vertices.df$screen_name,
      stringsAsFactors = FALSE
  )
  for(n in names(verts)){
    g <- set_vertex_attr(g,n,value=verts[,n])
  }
  plot(
    g,
    vertex.size=4,
    vertex.label=NA,
    main = "User Mention Plot (Boston)"
  )
}

if(nrow(usermentions.df) > 0){
  s <- sigmajs()
  s <- sg_from_igraph(s,g)
  s <- sg_settings(s,defaultEdgeColor="#AAA", edgeColor="default",minNodeSize=1,maxNodeSize=4)
  s
}

if(nrow(usermentions.df) > 0){
  g <- set_edge_attr(g,"title",E(g),paste(edge_attr(g,"created_at"),edge_attr(g,"status_text"),sep=": "))

  v <- visIgraph(g)
  v <- visEdges(
    v,
    color="gray"
  )
  v <- visNodes(
    v,
    size = 15
  )
  v
}

retweet.query <- sprintf("SELECT status_retweet.screen_name as retweeter,status_original.screen_name as retweeted, status_retweet.id as status_id, status_retweet.text as retweet_text, status_original.text as original_text, status_retweet.created_at as created_at FROM status as status_retweet JOIN status as status_original ON status_retweet.retweet_status_id = status_original.id JOIN search_status ON status_retweet.id = search_status.status_id JOIN query_text ON search_status.query_id = query_text.id WHERE status_original.created_at > '%s' AND status_retweet.retweet = 1 AND query_text.location = 'Boston' GROUP BY retweeter,retweeted;",format(Sys.Date()-7))
retweets.df <- DBI::dbGetQuery(con,retweet.query)
if(nrow(retweets.df) > 0){
  g <- graph_from_data_frame(retweets.df)
  vertices.df <- data.frame(index=as.integer(V(g)), screen_name = names(V(g)),stringsAsFactors = FALSE)
}

if(nrow(retweets.df) > 0){
  ec <- centr_eigen(g,directed=FALSE)
  df <- data.frame(
    Screen_Name = paste(
      "@",
      vertices.df$screen_name,
      sep=""
    ),
    Eig_Centrality = as.numeric(ec$vector)
  )
  df <- df[order(df[,2],decreasing=TRUE),]
  knitr::kable(
    df[1:10,],
    caption = "Retweet Network Eigenvector Centrality (Boston)"
  )
}

if(nrow(retweets.df) > 0){
  bc <- centr_betw(g,directed=FALSE)
  df <- data.frame(
    Screen_Name = paste(
      "@",
      vertices.df$screen_name,
      sep=""
    ),
    Bet_Centrality = as.numeric(bc$res)
  )
  df <- df[order(df[,2],decreasing=TRUE),]
  knitr::kable(
    df[1:10,],
    caption = "Retweet Network Betweenness Centrality (Boston)"
  )
}

if(nrow(retweets.df) > 0){
  verts <- data.frame(
      name = vertices.df$screen_name,
      label = vertices.df$screen_name,
      title = vertices.df$screen_name,
      stringsAsFactors = FALSE
  )

  for(n in names(verts)){
    g <- set_vertex_attr(g,n,value=verts[,n])
  }

  plot(g,vertex.color=vertex_attr(g,"color"),vertex.size=4,vertex.label=NA)
}

if(nrow(retweets.df) > 0){
  s <- sigmajs()
  s <- sg_from_igraph(s,g)
  s <- sg_settings(s,defaultEdgeColor="#AAA", edgeColor="default",minNodeSize=1,maxNodeSize=4)
  s
}

if(nrow(retweets.df) > 0){
  g <- set_edge_attr(g,"title",E(g),paste(edge_attr(g,"created_at"),edge_attr(g,"retweet_text"),sep=": "))

  v <- visIgraph(g)
  v <- visEdges(
    v,
    color="gray"
  )
  v <- visNodes(
    v,
    size = 15
  )
  v
}

DBI::dbDisconnect(con)